<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <title>Finding cache candidates</title>

 </head>
 <body><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.cache-efficiency.html">Measuring cache efficiency</a></div>
 <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-qc.cache-candidates" class="section">
  <h2 class="title">Finding cache candidates</h2>
  <p class="para">
   A statement should be considered for caching if it is executed often and has
   a long run time. Cache candidates are found by creating a list of statements
   sorted by the product of the number of executions multiplied by the
   statements run time. The function
   <span class="function"><a href="function.mysqlnd-qc-get-query-trace-log.html" class="function">mysqlnd_qc_get_query_trace_log()</a></span>
   returns a query log which help with the task.
  </p>
  <p class="para">
   Collecting a query trace is a slow operation. Thus, it is disabled by default.
   The PHP configuration directive
   <a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.collect-query-trace" class="link"><em>mysqlnd_qc.collect_query_trace</em></a>
   is used to enable it. The functions trace contains one entry for every
   query issued before the function is called.
  </p>
  <p class="para">
   <div class="example" id="example-1825">
    <p><strong>Example #1 Collecting a query trace</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_query_trace=1</pre>
</div>
    </div>

    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br /></span><span style="color: #FF8000">/*&nbsp;connect&nbsp;to&nbsp;MySQL&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"user"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"schema"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"port"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/*&nbsp;dummy&nbsp;queries&nbsp;to&nbsp;fill&nbsp;the&nbsp;query&nbsp;trace&nbsp;*/<br /></span><span style="color: #007700">for&nbsp;(</span><span style="color: #0000BB">$i&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">0</span><span style="color: #007700">;&nbsp;</span><span style="color: #0000BB">$i&nbsp;</span><span style="color: #007700">&lt;&nbsp;</span><span style="color: #0000BB">2</span><span style="color: #007700">;&nbsp;</span><span style="color: #0000BB">$i</span><span style="color: #007700">++)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;1&nbsp;AS&nbsp;_one&nbsp;FROM&nbsp;DUAL"</span><span style="color: #007700">);<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">free</span><span style="color: #007700">();<br />}<br /><br /></span><span style="color: #FF8000">/*&nbsp;dump&nbsp;trace&nbsp;*/<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">mysqlnd_qc_get_query_trace_log</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程会输出：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
array(2) {
  [0]=&gt;
  array(8) {
    [&quot;query&quot;]=&gt;
    string(26) &quot;SELECT 1 AS _one FROM DUAL&quot;
    [&quot;origin&quot;]=&gt;
    string(102) &quot;#0 qc.php(7): mysqli-&gt;query(&#039;SELECT 1 AS _on...&#039;)
#1 {main}&quot;
    [&quot;run_time&quot;]=&gt;
    int(0)
    [&quot;store_time&quot;]=&gt;
    int(25)
    [&quot;eligible_for_caching&quot;]=&gt;
    bool(false)
    [&quot;no_table&quot;]=&gt;
    bool(false)
    [&quot;was_added&quot;]=&gt;
    bool(false)
    [&quot;was_already_in_cache&quot;]=&gt;
    bool(false)
  }
  [1]=&gt;
  array(8) {
    [&quot;query&quot;]=&gt;
    string(26) &quot;SELECT 1 AS _one FROM DUAL&quot;
    [&quot;origin&quot;]=&gt;
    string(102) &quot;#0 qc.php(7): mysqli-&gt;query(&#039;SELECT 1 AS _on...&#039;)
#1 {main}&quot;
    [&quot;run_time&quot;]=&gt;
    int(0)
    [&quot;store_time&quot;]=&gt;
    int(8)
    [&quot;eligible_for_caching&quot;]=&gt;
    bool(false)
    [&quot;no_table&quot;]=&gt;
    bool(false)
    [&quot;was_added&quot;]=&gt;
    bool(false)
    [&quot;was_already_in_cache&quot;]=&gt;
    bool(false)
  }
}
</pre></div>
    </div>
   </div>
  </p>
  <p class="para">
   Assorted information is given in the trace. Among them
   timings and the origin of the query call. The origin property
   holds a code backtrace to identify the source of the query.
   The depth of the backtrace can be limited with
   the PHP configuration directive
   <a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.query-trace-bt-depth" class="link"><em>mysqlnd_qc.query_trace_bt_depth</em></a>.
   The default depth is <em>3</em>.
  </p>
  <p class="para">
   <div class="example" id="example-1826">
    <p><strong>Example #2 Setting the backtrace depth with the <em>mysqlnd_qc.query_trace_bt_depth</em> ini setting</strong></p>
    <div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_query_trace=1</pre>
</div>
    </div>

    <div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB">&lt;?php<br /></span><span style="color: #FF8000">/*&nbsp;connect&nbsp;to&nbsp;MySQL&nbsp;*/<br /></span><span style="color: #0000BB">$mysqli&nbsp;</span><span style="color: #007700">=&nbsp;new&nbsp;</span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"user"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"password"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"schema"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"port"</span><span style="color: #007700">,&nbsp;</span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP&nbsp;TABLE&nbsp;IF&nbsp;EXISTS&nbsp;test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE&nbsp;TABLE&nbsp;test(id&nbsp;INT)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT&nbsp;INTO&nbsp;test(id)&nbsp;VALUES&nbsp;(1),&nbsp;(2),&nbsp;(3)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/*&nbsp;dummy&nbsp;queries&nbsp;to&nbsp;fill&nbsp;the&nbsp;query&nbsp;trace&nbsp;*/<br /></span><span style="color: #007700">for&nbsp;(</span><span style="color: #0000BB">$i&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">0</span><span style="color: #007700">;&nbsp;</span><span style="color: #0000BB">$i&nbsp;</span><span style="color: #007700">&lt;&nbsp;</span><span style="color: #0000BB">3</span><span style="color: #007700">;&nbsp;</span><span style="color: #0000BB">$i</span><span style="color: #007700">++)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$res&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT&nbsp;id&nbsp;FROM&nbsp;test&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;"&nbsp;</span><span style="color: #007700">.&nbsp;</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">real_escape_string</span><span style="color: #007700">(</span><span style="color: #0000BB">$i</span><span style="color: #007700">));<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$res</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">free</span><span style="color: #007700">();<br />}<br /><br /></span><span style="color: #0000BB">$trace&nbsp;</span><span style="color: #007700">=&nbsp;</span><span style="color: #0000BB">mysqlnd_qc_get_query_trace_log</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$summary&nbsp;</span><span style="color: #007700">=&nbsp;array();<br />foreach&nbsp;(</span><span style="color: #0000BB">$trace&nbsp;</span><span style="color: #007700">as&nbsp;</span><span style="color: #0000BB">$entry</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(!isset(</span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]]))&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]]&nbsp;=&nbsp;array(<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">"executions"&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">1</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #DD0000">"time"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'run_time'</span><span style="color: #007700">]&nbsp;+&nbsp;</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'store_time'</span><span style="color: #007700">],<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;);<br />&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;else&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]][</span><span style="color: #DD0000">'executions'</span><span style="color: #007700">]++;<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]][</span><span style="color: #DD0000">'time'</span><span style="color: #007700">]&nbsp;+=&nbsp;</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'run_time'</span><span style="color: #007700">]&nbsp;+&nbsp;</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'store_time'</span><span style="color: #007700">];<br />&nbsp;&nbsp;&nbsp;&nbsp;}<br />}<br /><br />foreach&nbsp;(</span><span style="color: #0000BB">$summary&nbsp;</span><span style="color: #007700">as&nbsp;</span><span style="color: #0000BB">$query&nbsp;</span><span style="color: #007700">=&gt;&nbsp;</span><span style="color: #0000BB">$details</span><span style="color: #007700">)&nbsp;{<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"%45s:&nbsp;%5dms&nbsp;(%dx)\n"</span><span style="color: #007700">,<br />&nbsp;&nbsp;&nbsp;&nbsp;</span><span style="color: #0000BB">$query</span><span style="color: #007700">,&nbsp;</span><span style="color: #0000BB">$details</span><span style="color: #007700">[</span><span style="color: #DD0000">'time'</span><span style="color: #007700">],&nbsp;</span><span style="color: #0000BB">$details</span><span style="color: #007700">[</span><span style="color: #DD0000">'executions'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">?&gt;</span>
</span>
</code></div>
    </div>

    <div class="example-contents"><p>以上例程的输出类似于：</p></div>
    <div class="example-contents screen">
<div class="cdata"><pre>
                    DROP TABLE IF EXISTS test:     0ms (1x)
                    CREATE TABLE test(id INT):     0ms (1x)
    INSERT INTO test(id) VALUES (1), (2), (3):     0ms (1x)
             SELECT id FROM test WHERE id = 0:    25ms (1x)
             SELECT id FROM test WHERE id = 1:    10ms (1x)
             SELECT id FROM test WHERE id = 2:     9ms (1x)
</pre></div>
    </div>
   </div>
  </p>
 </div><hr /><div class="manualnavbar" style="text-align: center;">
 <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div>
 <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.cache-efficiency.html">Measuring cache efficiency</a></div>
 <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
 <div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
